from fileinput import close

import openpyxl
import os
from openpyxl.drawing.image import Image

#判断文件是否存在
def is_file(file_path : str = None) -> bool:
    if os.path.isfile(file_path):
        return True
    else:
        return False


class EditExcel:

    #初始化，传入文件路径
    def __init__(self, file_src_path : str = None, file_save_path : str = None):
        self.file_src_path = file_src_path
        self.file_save_path = file_save_path

        if is_file(self.file_src_path):
            self.work = openpyxl.load_workbook(self.file_src_path)
        else:
            print(f"{self.file_src_path}文件路径不存在！")
        
        # if file_save_path is None:
        #     self.file_save_path = file_src_path
        # else:
        #     self.file_save_path = file_save_path

    #保存Excel文件
    def save(self ,is_save_as_src : bool = False):

        if is_save_as_src:
            self.work.save(self.file_src_path)
            return True
        if self.file_save_path is not None:
            self.work.save(self.file_save_path)
            return True
        else:
            if is_file(self.file_src_path):
                self.work.save(self.file_src_path)
                return True
            else:
                print(f"{file_src_path}文件路径不存在！")
                return False

    #判断value所处单元格
    def get_cell_by_value(self, sheet_name : str, value : str) -> tuple:
        work = self.work[sheet_name]
        for row in work.rows:
            for cell in row:
                if cell.value == value:
                    return cell.row, cell.column

    #将字符串写入单元格
    def set_cell_value(self, sheet_name : str, cell : tuple[int, int], value : str):
        work = self.work[sheet_name]
        work.cell(row=cell[0], column=cell[1]).value = value
        self.save(is_save_as_src=True)

    #将图片写入单元格
    def set_cell_image(self, sheet_name : str, cell : tuple[int, int], image_path : str):
        row , column= cell
        work = self.work[sheet_name]
        img = Image(image_path)
        work.add_image(img, chr(column + 64) + str(row))
        print(f"向单元格'{row}'行'{column}'列插入图片成功")

    #添加Sheet
    def add_sheet(self, sheet_name : str):

        self.work.create_sheet(title=sheet_name)
        print("添加Sheet成功")

    #合并单元格
    def merge_cells(self, sheet_name : str, start_cell : tuple[int, int], end_cell : tuple[int, int]):
        work = self.work[sheet_name]
        start = chr(start_cell[1] + 64) + str(start_cell[0])
        end = chr(end_cell[1] + 64) + str(end_cell[0])
        print(start,end)
        work.merge_cells(
            start + ":" + end
        )

    #设置行高
    def set_row_height(self, sheet_name : str, row_height : dict[int, int]):
        work = self.work[sheet_name]
        for row, height in row_height.items():
            work.row_dimensions[row].height = height

    #设置列宽
    def set_column_width(self, sheet_name : str, column_width : dict[int, int]):
        work = self.work[sheet_name]
        for column, width in column_width.items():
            work.column_dimensions[chr(column + 64)].width = width

class ReadExcel:

    def __init__(self, file_path : str = None):
        self.file_path = file_path
        if is_file(self.file_path):
            self.work = openpyxl.load_workbook(self.file_path)
        else:
            print(f"{self.file_path}文件路径不存在！")

    #判断value所处单元格
    def get_cell_by_value(self, sheet_name : str, value : str) -> tuple:
        work = self.work[sheet_name]
        for row in work.rows:
            for cell in row:
                if cell.value == value:
                    return cell.row, cell.column

    #获取Sheet名称
    def get_sheet_names(self) -> list:
        return self.work.sheetnames

    #获取行数
    def get_row_count(self, sheet_name : str) -> int:
        work = self.work[sheet_name]
        return work.max_row

    #获取列数
    def get_column_count(self, sheet_name : str) -> int:
        work = self.work[sheet_name]
        return work.max_column

    #获取单元格值
    def get_cell_value(self, sheet_name : str, cell : tuple[int, int]) -> str:
        work = self.work[sheet_name]
        return work.cell(row=cell[0], column=cell[1]).value

#测试
if __name__ == '__main__':

    img_path = 'D://Excel//excel//v1//test.jpg'
    file_src_path = 'D://Excel//excel//v1//test.xlsx'
    file_save_path = 'D://Excel//excel//v1//test_save.xlsx'
    EE=EditExcel(file_src_path = file_src_path,
                 file_save_path=  file_save_path)

    if is_file(file_src_path):
        result=EE.get_cell_by_value("Sheet1", "hello world")
        EE.set_cell_value("Sheet1", (2,3), "hello world1")
        # EE.set_cell_value("Sheet1", (1, 1), "hello world")
        EE.add_sheet("test")
        for i in range(1,50):
            EE.set_cell_image("test",(i,1),img_path)
        EE.save()

    else:
        print('文件不存在')

